package group32.server;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import com.google.gwt.i18n.client.DateTimeFormat;

import group32.shared.User;
import group32.shared.UserFriend;


public class DBQuery extends DBConn {

	public DBQuery(){
		
	}
	
	/**
     * save feed back
     * @param feedbackData
     * @return
     */
    public boolean saveUser(User u) {

    	boolean result=false;
    	int uid=-1;
            String sql = "INSERT INTO `users` (username,firstname,lastname,email,password,usertype) values('" +
                    u.username + "', '" +
                    u.firstname + "', '" +
                    u.lastname + "', '" +
                    u.email + "', '" +
                    u.password+"', '"+
                    u.usertype + "');";

            System.out.println(sql);
                    
            try {
                    Connection connection = getConn();
                    Statement update = connection.createStatement();
                    update.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
                    ResultSet rs=update.getGeneratedKeys();
                    if(rs.next()){
            			result= true;  
            			uid=rs.getInt(1);
            			System.out.println(uid);
            			String ins = null;
						if(uid!=-1)
            				if(u.usertype=='S')
            				 ins = "INSERT INTO `students`(studentID) values("+uid+");";
            				else
              				  ins = "INSERT INTO `professors`(staffID) values("+uid+");";
            			int res=update.executeUpdate(ins);
                    }
                        else
                        	result=false;
            			connection.close();
        		}
           
            catch (SQLException e) {
        			// TODO Auto-generated catch block
            	 System.err.println("Mysql Statement Error: " + sql);
            	 e.printStackTrace();
            	 result= false;
        		}

            return result;
    }

    
    public boolean updateUser(int uid,String utype, User u) {

    	boolean result=false;  
    	boolean success=false;
    	String sql="";
    	System.out.println(u.interests);
    	String[]interests=u.interests.split(",");
    	System.out.println(interests.length);
		 SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd");
    	String s=df.format(u.dob);
             sql = "UPDATE `users` SET firstname='"+u.firstname+"',lastname='"+u.lastname+"',email='"+u.email+"'" +
             		",dob='"+s+"',gender='"+u.gender+"',address='"+u.address+"',school='"+u.school+"' where userID="+uid+";";
            System.out.println(sql);
                    
            try {
                    Connection connection = getConn();
                    Statement update = connection.createStatement();
                    if (update.executeUpdate(sql)>0) {
            			result= true;  
            			if(interests.length>0){
            			String insert =null ;
            			for(int i=0;i<interests.length;i++){
            				insert = "INSERT into userinterest values("+uid+",'"+interests[i].trim()+"') ON DUPLICATE KEY UPDATE interest='"+interests[i]+"'";
            			update.executeUpdate(insert);
            			}
            			}
            			connection.close();
        			}
                    if(utype.equals("S"))
                        success=updateStudent(uid,u);
                    else
                        success=updateProfessor(uid,u);
                    	
        		}
           
            catch (SQLException e) {
        			// TODO Auto-generated catch block
            	 System.err.println("Mysql Statement Error: " + sql);
            	 e.printStackTrace();
            	 result= false;
        		}
            return result&&success;
    }
   
    public boolean updateProfessor(int uid, User u) {

    	boolean result=false;  
    	String sql="";
    		sql="INSERT INTO `professors`(staffID,roomnumber,title,researcharea) values("+uid+","+u.roomNumber+",'"+u.title+"','"+u.researchArea+"') ON DUPLICATE KEY UPDATE roomnumber="+u.roomNumber+"," +
             		"title='"+u.title+"', researcharea='"+u.researchArea+"'";
            System.out.println(sql);
                    
            try {
                    Connection connection = getConn();
                    Statement update = connection.createStatement();
                    if (update.executeUpdate(sql)>0) {
            			result= true;  
            			connection.close();
        			}
        		}
           
            catch (SQLException e) {
        			// TODO Auto-generated catch block
            	 System.err.println("Mysql Statement Error: " + sql);
            	 e.printStackTrace();
            	 result= false;
        		}
                   

            return result;
    }
    
    public boolean updateStudent(int uid, User u) {

    	boolean result=false;  
    	boolean success=false;  
    	String sql="";
    	
             sql = "INSERT INTO `students`(studentID,major,GPA,YEAR) values("+uid+",'"+u.major+"',"+u.GPA+","+u.year+") ON DUPLICATE KEY UPDATE major='"+u.major+"',GPA="+u.GPA+", YEAR="+u.year;
            System.out.println(sql);
                    
            try {
                    Connection connection = getConn();
                    Statement update = connection.createStatement();
                    if (update.executeUpdate(sql)>0) {
            			result= true;  
            			connection.close();
        			}
                   success= updateAdvisor(uid,u);
        		}
           
            catch (SQLException e) {
        			// TODO Auto-generated catch block
            	 System.err.println("Mysql Statement Error: " + sql);
            	 e.printStackTrace();
            	 result= false;
        		}
                   

            return result&&success;
    }
    
    /**
     * 
     * @param uid
     * @param u
     */
    private boolean updateAdvisor(int uid, User u) {
    	boolean result=false;  
    	String sql="";
    	
             sql = "INSERT INTO `advises`(advisorID,advisesID,yearsAdvised) values("+u.advises+","+uid+","+u.yearsAdvised+") ON DUPLICATE KEY UPDATE yearsAdvised="+u.yearsAdvised+";";
    	
            System.out.println(sql);
                    
            try {
                    Connection connection = getConn();
                    Statement update = connection.createStatement();
                    if (update.executeUpdate(sql)>0) {
            			result= true;  
            			connection.close();
        			}
        		}
           
            catch (SQLException e) {
        			// TODO Auto-generated catch block
            	 System.err.println("Mysql Statement Error: " + sql);
            	 e.printStackTrace();
            	 result= false;
        		}
                   

            return result;// TODO Auto-generated method stub
		
	}

    /**
     * 
     * @param uid
     * @param u
     */
    private boolean addAdvisor(int uid, User u) {
    	boolean result=false;  
    	String sql="";
    	
             sql = "INSERT INTO `advises`(advisorID,advisesID,yearsAdvised) values("+u.advises+","+uid+","+u.yearsAdvised+";";
    	
            System.out.println(sql);
                    
            try {
                    Connection connection = getConn();
                    Statement update = connection.createStatement();
                    if (update.executeUpdate(sql)>0) {
            			result= true;  
            			connection.close();
        			}
        		}
           
            catch (SQLException e) {
        			// TODO Auto-generated catch block
            	 System.err.println("Mysql Statement Error: " + sql);
            	 e.printStackTrace();
            	 result= false;
        		}
                   

            return result;// TODO Auto-generated method stub
		
	}
	/**
     * Updates login credentials
     * @param uid
     * @param password
     * @return
     */
    public boolean UpdateSecurity(String uname, String oldP,String password ) {

    	boolean result=false;
    	
            String sql = "UPDATE `users` SET password='"+password+ "' where username='"+uname+"' and password='"+oldP+"';";

            System.out.println(sql);
                    
            try {
                    Connection connection = getConn();
                    Statement update = connection.createStatement();
                    if (update.executeUpdate(sql)>0) {
            			result= true;  
            			connection.close();
        			}
                  
        		}
           
            catch (SQLException e) {
        			// TODO Auto-generated catch block
            	 System.err.println("Mysql Statement Error: " + sql);
            	 e.printStackTrace();
            	 result= false;
        		}
                   

            return result;
    }
    
    /**
     * get potential advisors
     * @return
     */
    public ArrayList<User> getFaculty() {

    	
    	ArrayList<User> result = new ArrayList<User>();
            String sql = "SELECT u.userID,u.firstname,u.lastname FROM `users` u where u.usertype='P';";

            System.out.println(sql);
                    
            try {
                    Connection connection = getConn();
                    Statement sel = connection.createStatement();
                    ResultSet rs=sel.executeQuery(sql);
                    while(rs.next()){
                    	User u=new User();
                    	u.userID=rs.getInt("userID");
                    	u.firstname=rs.getString("firstname");
                    	u.lastname=rs.getString("lastname");
                    	result.add(u);
                    }
                    sel.close();
                    rs.close();
                    connection.close();
                    connection.close();
        		}
           
            catch (SQLException e) {
        			// TODO Auto-generated catch block
            	 System.err.println("Mysql Statement Error: " + sql);
            	 e.printStackTrace();
            	 result=null;
        		}
                   
            return result;
           
    }
    
    /**
     * get user details
     * @param uid
     * @param utype
     * @return
     */
    public User getUser(int uid,String utype) {

    	String sql=""; 
    	User u=new User();
    	int aid=-1;
    		if(utype.equals("S"))
    			sql= "SELECT u.*,s.major,s.GPA,s.YEAR,a.advisorID as advisor,a.yearsAdvised as years FROM `users` u left join `students` s on u.userID=s.studentID left join `advises` a on s.studentID=a.advisesID where u.userID="+uid;
    		else
    			sql= "SELECT u.*,p.title,p.roomnumber,p.researcharea, COUNT(a.advisesID) as students FROM `users` u left join `advises` a on u.userID=a.advisorID left join `professors` p on a.advisorID=p.staffID where u.userID="+uid;	
           
    		System.out.println(sql);
                    
            try {
                    Connection connection = getConn();
                    Statement sel = connection.createStatement();
                    ResultSet rs=sel.executeQuery(sql);
                    while(rs.next()){
                    	u.userID=rs.getInt("userID");
                    	u.username=rs.getString("username");
                    	u.firstname=rs.getString("firstname");
                    	u.lastname=rs.getString("lastname");
                    	u.email=rs.getString("email");
                    	u.dob=rs.getDate("dob");
                    	if(rs.getString("gender")!=null)
                    		u.gender=rs.getString("gender").charAt(0);
                    	else
                        	u.gender=' ';
                    	u.address=rs.getString("address");
                    	u.school=rs.getString("school");
                    	u.usertype=rs.getString("usertype").charAt(0);
                    	if(utype.equals("S")){
	                    	u.major=rs.getString("major");
	                    	u.GPA=rs.getFloat("GPA");
	                    	u.year=rs.getInt("YEAR");
	                    	aid=rs.getInt("advisor");
	                    	u.yearsAdvised=rs.getInt("years");
                    	}
                    	if(utype.equals("P")){
	                    	u.title=rs.getString("title");
	                    	u.roomNumber=rs.getInt("roomnumber");
	                    	u.researchArea=rs.getString("researcharea");
	                    	u.advises=rs.getInt("students");
                    	}
                    }
                    sel.close();
                    rs.close();
                    
                    
                    if(aid!=-1){
                    	System.out.println("Advisor "+aid);
                    	u.advisor=getAdvisor(aid);
                    }
                    u.interests=getInterests(uid);
                    
                    connection.close();
        		}
           
            catch (SQLException e) {
        			// TODO Auto-generated catch block
            	 System.err.println("Mysql Statement Error: " + sql);
            	 e.printStackTrace();
        		}
                   
            return u;
           
    }
    
    public String getAdvisor(int id) {
    	String a="";
    	try{
    		 Connection connection = getConn(); 
    		 Statement select = connection.createStatement();
             ResultSet advisor=select.executeQuery("Select CONCAT(u.firstname,' ',u.lastname) as advisor from users u where userID="+id);
             while(advisor.next())
             	a=advisor.getString("advisor");
             select.close();
             advisor.close();
    	}
    	  catch (SQLException e) {
  			// TODO Auto-generated catch block
      	 System.err.println("Mysql Statement Error: "+"Select u.firstname+' '+u.lastname as advisor from users u where userID="+id);
      	 e.printStackTrace();
  		}
    	System.out.println(a);
    	return a;
    	 
    }
    
    public String getInterests(int id){
    	String a="";
    	try{
    		 Connection connection = getConn(); 
    		 Statement select = connection.createStatement();
             ResultSet interest=select.executeQuery("Select interest from userinterest u where userID="+id);
             while(interest.next())
             	a+=interest.getString("interest")+",";
             select.close();
             interest.close();
    	}
    	  catch (SQLException e) {
  			// TODO Auto-generated catch block
      	 System.err.println("Mysql Statement Error: " );
      	 e.printStackTrace();
  		}
    	return a;
    	 
    }

	public String getPersonAndNeighbors(int person,String type) {
		// TODO Auto-generated method stub
		String a="";
		int tempid=-1;int data9=-1;
    	try{
    		 Connection connection = getConn(); 
    		 Statement select = connection.createStatement();
             int data6=select.executeUpdate("drop table if exists `tempUF`;");
             int data7=select.executeUpdate("create table `tempUF`(userID int(11),friendsID int(11))");              
             int data8=select.executeUpdate("insert into `tempUF` (Select us.userID,us.friendsID from `friends` us where us.userID="+person+");");
             if(type.equals("S"))
             data9=select.executeUpdate("insert into `tempUF` (Select us.advisesID,us.advisorID from `advises` us where us.advisesID="+person+" and us.advisorID NOT IN" +
             		"(SELECT friendsID from `tempUF` where userID="+person+"));");
             else
             data9=select.executeUpdate("insert into `tempUF` (Select us.advisorID,us.advisesID from `advises` us where us.advisorID="+person+" and us.advisesID NOT IN" +
             		"(SELECT friendsID from `tempUF` where userID="+person+"));");	
             System.out.println(data9); 
             int data=select.executeUpdate("drop table if exists `temp`;");
             int data2=select.executeUpdate("create table `temp`(userID int(11),firstname varchar(45),lastname varchar(45), fID int(11), usertype char(1),username varchar(20))");              
             //data.close();            
             int data1=select.executeUpdate("insert into `temp` (Select us.userID,u.firstname,u.lastname, us.friendsID,u.usertype,u.username from `tempUF` us,`users` u where us.userID="+person+" and u.userID=us.userID);");
             ResultSet data11=select.executeQuery("select t.userID,CONCAT(t.firstname,' ',t.lastname) as name,t.fID ,CONCAT(us.firstname,' ',us.lastname) as fname,us.usertype as ftype,t.usertype,us.username as flogon,t.username as logon from `users` us,`temp` t where t.fID=us.userID;");
             int count=0;
             while(data11.next()){
            	 int uid=data11.getInt("userID");
            	 int fid=data11.getInt("fID");
            	 if(uid!=tempid){
            		 if(count!=0)
            			 a+="</hasFriend></user>";
            		 a+="<user><id>"+uid+"</id><name>"+data11.getString("name")+"</name><type>"+data11.getString("usertype")+"</type><uname>"+data11.getString("logon")+"</uname>";
            	 if(fid!=0)
            		 a+="<hasFriend><friend><id>"+fid+"</id><name>"+data11.getString("fname")+"</name><ftype>"+data11.getString("ftype")+"</ftype><funame>"+data11.getString("flogon")+"</funame></friend>";
            	 }
            	 else{
            		 a+="<friend><id>"+fid+"</id><name>"+data11.getString("fname")+"</name><ftype>"+data11.getString("ftype")+"</ftype><funame>"+data11.getString("flogon")+"</funame></friend>";
            	}
            	count++;
             	tempid=uid;
             }
             data11.close();
             int data13=select.executeUpdate("drop table `tempUF`;");
             int data3=select.executeUpdate("drop table `temp`;");
             /**if(a.isEmpty()){
            	 ResultSet rs13=select.executeQuery("select u.userID,CONCAT(u.firstname,' ',u.lastname) as name,u.usertype as type from users u where u.userID="+person);
            	 while(rs13.next()){
            		 a+="<user><id>"+rs13.getInt("userID")+"</id><name>"+rs13.getString("name")+"</name><type>"+rs13.getString("type")+"</type><hasFriend>";
            	 }
             }*/
             select.close();    	}
    	  catch (SQLException e) {
  			// TODO Auto-generated catch block
      	 System.err.println("Mysql Statement Error: " );
      	 e.printStackTrace();
  		}
    	
    	System.out.println(a);
    	return a;
	}
	
	
	
	
}
